<?php
/*
 *	统计管理模块
 */
namespace app\admin\controller;

use think\facade\Db;
use app\common\model\DealMain;
use app\common\model\DealInfo;
use app\common\model\SystemConfig as CONFIG;

class Collect extends Base{
	protected $middleware = ['AdminCheck','AdminPermission'];
	
	private $tableName='deal_main';
	private $infoName='deal_info';
	private $clothName='cloth_main';
	private $colorName='customer_sample';
	private $customerName='customer_main';

	//统计
	public function sale(){
		//$sql="select month as month,sum(total) as total from jk_deal where year(from_unixtime(create_time)) = 2018 group by month ";
		
		$year=I('get.year',date('Y'));
		$month=I('get.month',0);
		$year_list=[];
		$month_list=[];
		
		//获取订单最早的年份
		if(CONFIG::get('info.other.history_sale',0)==1){			//判断开关或隐藏
			$old_year=Db::name($this->tableName)->where(['uuid'=>$this->uuid])->order('create_time asc')->value('create_time');
		}else{
			$old_year="";
		}		
		$old_year=$old_year!=''?date('Y',$old_year):date('Y');

		if($month<=0){		//按年统计数据
			$sql="select month as kname,sum(total) as total,sum(poundage) as poundage,sum(cost) as cost,count(id) as count,count(distinct cusid) as cus from ".env('database.prefix').$this->tableName." where uuid = ".$this->uuid." and year = ".$year." and status=1 group by month";
			$sql2="select deal.month as kname,sum(info.roll) as roll from ".env('database.prefix').$this->tableName." as deal inner join ".env('database.prefix') . $this->infoName . " as info on deal.id=info.did where deal.uuid = ".$this->uuid." and deal.year = ".$year." and info.unit='KG' and deal.status=1 group by deal.month ";
			$sql3="select month as kname,sum(money) as g_profit from ".env('database.prefix')."bill_main  where uuid = ".$this->uuid." and year=".$year." and type=1 group by month";
			$sql4="select month as kname,sum(money) as invest from ".env('database.prefix')."bill_main  where uuid = ".$this->uuid." and year=".$year." and type=0 group by month";
			
			$addcus_sql="select month(FROM_UNIXTIME(create_time)) as kname,count(id) as dows from ".env('database.prefix')."customer_main where uuid = ".$this->uuid." and FROM_UNIXTIME(create_time,'%Y') = ".$year." and status=1 group by month(from_unixtime(create_time))";
			$length=12;
			
			
			//获取历年列表菜单
			for($i=$old_year;$i<=date('Y');$i++){
				$year_list[]=[
					'title'=>$i.'年',
					'id'=>$i,
					'url'=>(string)U('collect/sale',['year'=>$i]),
				];
			}
			$year_list=json_encode($year_list);
			
			//获取12个月菜单
			for($i=1;$i<=$length;$i++){
				$month_list[]=[
					'title'=>$i.'月',
					'id'=>$i,
					'url'=>(string)U('collect/sale',['year'=>$year,'month'=>$i]),
				];
			}
			$month_list=json_encode($month_list);
			
		}else{				//按月统计数据
			$sql="select day as kname,sum(total) as total,sum(poundage) as poundage,sum(cost) as cost,count(id) as count,count(distinct cusid) as cus from ".env('database.prefix').$this->tableName." where uuid = ".$this->uuid." and year = ".$year." and month = ".$month." and status=1 group by day";
			$sql2="select day as kname,sum(info.roll) as roll from ".env('database.prefix').$this->tableName." as deal inner join ".env('database.prefix') . $this->infoName . " as info on deal.id=info.did where deal.uuid = ".$this->uuid." and deal.year = ".$year." and deal.month = ".$month." and info.unit='KG' and deal.status=1 group by deal.day ";
			$sql3="select day as kname,sum(money) as g_profit from ".env('database.prefix')."bill_main  where uuid = ".$this->uuid." and year=".$year." and month=".$month." and type=1 group by day";
			$sql4="select day as kname,sum(money) as invest from ".env('database.prefix')."bill_main  where uuid = ".$this->uuid." and year=".$year." and month=".$month." and type=0 group by day";
			$addcus_sql="select day(FROM_UNIXTIME(create_time)) as kname,count(id) as dows from ".env('database.prefix')."customer_main where uuid = ".$this->uuid." and FROM_UNIXTIME(create_time,'%Y') = ".$year." and FROM_UNIXTIME(create_time,'%m') = ".$month." and status=1 group by day(FROM_UNIXTIME(create_time)) ";
			$length=date("t",strtotime($year."-".$month."-01"));
		}

		//统计订单表
		$result = Db::query($sql);
		//统计订单明细数据
		$result2 = Db::query($sql2);
		//统计订单利润数据
		$result3 = Db::query($sql3);
		//统计开支数据
		$result4 = Db::query($sql4);
		//统计新增客户数
		$addcus_res = Db::query($addcus_sql);

		$data=[];
		$result2=column_array($result2,'roll','kname');
		$result3=column_array($result3,'g_profit','kname');
		$result4=column_array($result4,'invest','kname');
		$addcus_res=column_array($addcus_res,'dows','kname');
		foreach ($result as $val) {
			$roll=isset($result2[$val['kname']])?intval($result2[$val['kname']]):0;
			$g_profit=isset($result3[$val['kname']])?intval($result3[$val['kname']]):0;
			$invest=isset($result4[$val['kname']])?intval($result4[$val['kname']]):0;
			$dow=isset($addcus_res[$val['kname']])?intval($addcus_res[$val['kname']]):0;
			$val['roll']=$roll;
			$val['g_profit']=format_number($g_profit,'float',1);			//毛利润
			$val['invest']=format_number($invest,'float',1);				//开销
			$val['n_profit']=format_number(($g_profit+$invest),'float',1);	//净利润
			$val['total']=format_number($val['total'],'float',1);
			$val['dow']=$dow;
			$data[$val['kname']]=$val;
		}

		//补齐空的月份或日份
		$total=[];
		$count=[];
		$cost=[];
		$dow=[];
		$roll=[];
		$g_profit=[];
		$n_profit=[];
		$invest=[];
		for($i=1;$i<=$length;$i++){
			if(!isset($data[$i])){
				$data[$i]=[
					'month'=>$i,
					'total'=>0,
					'poundage'=>0,
					'cost'=>0,
					'g_profit'=>0,
					'n_profit'=>0,
					'count'=>0,
					'dow'=>0,
					'cus'=>0,
					'roll'=>0,
					'invest'=>0,
				];
			}
			$total[]=$data[$i]['total'];
			$cost[]=$data[$i]['cost'];
			$g_profit[]=$data[$i]['g_profit'];
			$invest[]=$data[$i]['invest'];
			$n_profit[]=$data[$i]['n_profit'];
			$count[]=$data[$i]['count'];
			$dow[]=$data[$i]['dow'];
			$cus[]=$data[$i]['cus'];
			$roll[]=$data[$i]['roll'];
		}
		
		ksort($data);
		
		$putdata=[
			'sensitive' => CONFIG::get('info.other.sensitive_data',0),
			'length'=>$length,
			'old_year'=>$old_year,
			'year_list'=>$year_list,
			'month_list'=>$month_list,
			'year'=>$year,
			'month'=>$month,
			'total'=>implode(',',$total),
			'count'=>implode(',',$count),
			'cost'=>implode(',',$cost),
			'g_profit'=>implode(',',$g_profit),
			'n_profit'=>implode(',',$n_profit),
			'invest'=>implode(',',$invest),
			'dow'=>implode(',',$dow),
			'cus'=>implode(',',$cus),
			'roll'=>implode(',',$roll),
			'T'=>$data,
		];
		return $this->fetch('',$putdata);
	}


	//读取产品销售情况
	public function product(){
		$data=F($this->uuid.'/collect/product');
		if($data==''){
			$data=$this->product_exe();
		}
		$T=$data;	
		return $this->fetch('',['T'=>$T]);
	}
	
	//清除统计缓存
	public function cls(){
		$cache = cache();
		$cache->clear();
		$this->success('更新成功！','/product');
	}

	//统计缓存函数
	public function product_exe(){

		$data=[];
		$buycount=[];
		$buydata=[];

		//查询编号产品有多少用户下单
		$sql='select a.number,count(a.number) as count from (select distinct b.number,a.cusid from '.env('database.prefix').$this->tableName.' as a join '.env('database.prefix').$this->infoName.' as b on a.id=b.did where a.status=1 and a.uuid = '.$this->uuid.') as a group by a.number';
		$result = Db::query($sql);
		$buycount=column_array($result,'count','number');

		//查询编号产品下单数量和金额
		$sql='select b.number,round(sum(b.count)) as count,round(sum(b.total)) as total from '.env('database.prefix').$this->tableName.' as a join '.env('database.prefix').$this->infoName.' as b on a.id=b.did where a.uuid = '.$this->uuid.' and and a.status=1 group by b.number';
		$result = Db::query($sql);
		$buydata=set_key($result,'number');

		//获取色卡记录
		$color=Db::name($this->colorName)->select()->toArray();
		foreach ($color as $v) {
			$card=explode('/',$v['card']);
			foreach ($card as $number){
				if(isset($data[$number])){
					$data[$number]['color']++;
				}else{
					$data[$number]=[
						'number'=>$number,
						'color'=>1,
						'customer'=>0,
						'count'=>0,
						'total'=>0,
						'seka'=>0,
						'kedanshu'=>0,
						'kedanjia'=>0
					];
					if(isset($buycount[$number])){
						$data[$number]['customer']=$buycount[$number];				//购买客户数
						$data[$number]['count']=$buydata[$number]['count'];			//销售总数量
						$data[$number]['total']=$buydata[$number]['total'];			//销售总金额
						
						$data[$number]['seka']=round($buycount[$number]/$data[$number]['count']*100,2);
						$data[$number]['kedanshu']=round($buycount[$number]/$data[$number]['count'],2);
						$data[$number]['kedanjia']=round($data[$number]['total']/$data[$number]['count'],2);	
						unset($buycount[$number]);
						unset($buydata[$number]);
					}
				}
			}
		}
		
		krsort($data);
		$data=array('time'=>time(),'data'=>$data);
		F($this->uuid.'/collect/product',$data);
		return $data;
	}

	//统计某年某月的销售数据
	public function rank(){
		$year=I('get.year',date('Y'));

		//获取订单最早的年份
		if(CONFIG::get('info.other.history_sale',0)==1){
			$old_year=Db::name($this->tableName)->where(['uuid'=>$this->uuid])->order('create_time asc')->value('create_time');
		}else{
			$old_year="";
		}
		$old_year=$old_year!=''?date('Y',$old_year):date('Y');

		$base_sql="select a.name,a.number,a.roll,b.cusid,b.year as y,b.month as m from ".env('database.prefix')."deal_info as a left join ".env('database.prefix')."deal_main as b on  a.did=b.id where b.uuid = ".$this->uuid." and b.status=1 and b.year=".$year;

		//查询全年热销产品大货
		//$sql='select number,any_value(name) as name,SUM(roll) as count from ('.$base_sql.' and a.roll>0 order by m asc) as deal GROUP BY deal.number order by count desc limit 0,50';
		$sql='select number,name,SUM(roll) as count from ('.$base_sql.' and a.roll>0 order by m asc) as deal GROUP BY deal.number order by count desc limit 0,50';
		$H1=$this->put_hot_data($sql,$year,1);
		
		//查询全年热销产品剪板
		//$sql='select number,any_value(name) as name,count(cusid) as count from ('.$base_sql.' and (a.unit="码" or a.unit="米") order by m asc) as deal GROUP BY deal.number order by count desc limit 0,50';
		$sql='select number,name,count(cusid) as count from ('.$base_sql.' and (a.unit="码" or a.unit="米") order by m asc) as deal GROUP BY deal.number order by count desc limit 0,50';
		$H2=$this->put_hot_data($sql,$year,2);

		//查询全年每月最热销的产品采购量
		//$sql='select m,number,any_value(name) as name,SUM(roll) as count from ('.$base_sql.' and a.roll>0 order by m asc) as deal GROUP BY deal.m,deal.number order by m asc,count desc';
		$sql='select m,number,name,SUM(roll) as count from ('.$base_sql.' and a.roll>0 order by m asc) as deal GROUP BY deal.m,deal.number order by m asc,count desc';
		$T1=$this->put_month_data($sql,$year,1);
		
		//查询全年每月最多客户采购大货的产品
		//$sql='select m,number,any_value(name) as name,count(cusid) as count from ('.$base_sql.' and a.roll>0 order by m asc) as deal GROUP BY deal.m,deal.number order by m asc,count desc';
		$sql='select m,number,name,count(cusid) as count from ('.$base_sql.' and a.roll>0 order by m asc) as deal GROUP BY deal.m,deal.number order by m asc,count desc';
		$T2=$this->put_month_data($sql,$year,2);

		//查询全年每月最多客户剪样的产品
		//$sql='select m,number,any_value(name) as name,count(cusid) as count from ('.$base_sql.' and (a.unit="码" or a.unit="米") order by m asc) as deal GROUP BY deal.m,deal.number order by m asc,count desc';
		$sql='select m,number,name,count(cusid) as count from ('.$base_sql.' and (a.unit="码" or a.unit="米") order by m asc) as deal GROUP BY deal.m,deal.number order by m asc,count desc';
		$T3=$this->put_month_data($sql,$year,3);

		//全年没有剪过板的产品
		$sql="select c.number,c.title,d.dnumber,d.name from ".env('database.prefix')."cloth_main as c left join (select distinct a.number as dnumber,a.name from ".env('database.prefix')."deal_info as a left join ".env('database.prefix')."deal_main as b on a.did=b.id where b.uuid = ".$this->uuid." and b.status=1 and b.year=".$year." and (a.unit='码' or a.unit='米')) as d on c.number=d.dnumber where d.dnumber is null";
		$E1=$this->put_no_sale($sql,$year,1);

		//全年没有拿过大货的产品
		$sql="select c.number,c.title,d.dnumber,d.name from ".env('database.prefix')."cloth_main as c left join (select distinct a.number as dnumber,a.name from ".env('database.prefix')."deal_info as a left join ".env('database.prefix')."deal_main as b on a.did=b.id where b.uuid = ".$this->uuid." and b.status=1 and b.year=".$year." and a.roll>0) as d on c.number=d.dnumber where d.dnumber is null";
		$E2=$this->put_no_sale($sql,$year,2);

		//拿样却没有复购大货的产品
		$E3=$this->repeat_buy($year);
		
		//获取历年列表菜单
		for($i=$old_year;$i<=date('Y');$i++){
			$year_list[]=[
				'title'=>$i.'年',
				'id'=>$i,
				'url'=>(string)U('collect/rank',['year'=>$i]),
			];
		}
		$year_list=json_encode($year_list);
		
		$year=$year;
		return $this->fetch('',[
			'year'=>$year,
			'old_year'=>$old_year,
			'year_list'=>$year_list,
			'month'=>0,
			'H1'=>$H1,
			'H2'=>$H2,
			'T1'=>$T1,
			'T2'=>$T2,
			'T3'=>$T3,
			'E1'=>$E1,
			'E2'=>$E2,
			'E3'=>$E3,
		]);
	}

	//缓存热销产品统计
	private function put_month_data($sql,$year,$id){
		$data=[];
		$now_month=1;
		$i=1;
		$exe=0;							//0=不缓存    1=生成缓存    2=获取缓存
		if(date('Y')!=$year){			//往年数据缓存，更新需删除缓存文件
			$data=F($this->uuid.'collect/'.$year.'/rank'.$id);
			if($data==""){
				$exe=1;
			}else{
				$exe=2;
			}
		}else{
			$exe=0;
		}

		//读取数据
		if($exe<2){
			$res=Db::query($sql);
			foreach ($res as $v) {
				if($now_month!=$v['m']){
					$i=1;
					$now_month=$v['m'];
				}
				if($i<=10){
					$data[$v['m']][$i]=$v;
					$i++;
				}
			}
			if($exe==1){
				F($this->uuid.'collect/'.$year.'/rank'.$id,$data);
			}
		}
		return $data;
	}

	//缓存全年热销产品
	private function put_hot_data($sql,$year,$id){
		$data=[];
		$i=1;
		$exe=0;							//0=不缓存    1=生成缓存    2=获取缓存
		if(date('Y')!=$year){			//往年数据缓存，更新需删除缓存文件
			$data=F($this->uuid.'collect/'.$year.'/hotsale'.$id);
			if($data==""){
				$exe=1;
			}else{
				$exe=2;
			}
		}else{
			$exe=0;
		}

		//读取数据
		if($exe<2){
			$data=Db::query($sql);
			if($exe==1){
				F($this->uuid.'collect/'.$year.'/hotsale'.$id,$data);
			}
		}
		return $data;
	}

	//缓存销售产品统计
	private function put_no_sale($sql,$year,$id){
		$data=[];
		$i=1;
		$exe=0;							//0=不缓存    1=生成缓存    2=获取缓存
		if(date('Y')!=$year){			//往年数据缓存，更新需删除缓存文件
			$data=F($this->uuid.'collect/'.$year.'/nosale'.$id);
			if($data==""){
				$exe=1;
			}else{
				$exe=2;
			}
		}else{
			$exe=0;
		}

		//读取数据
		if($exe<2){
			$data=Db::query($sql);
			if($exe==1){
				F($this->uuid.'collect/'.$year.'/nosale'.$id,$data);
			}
		}
		return $data;
	}

	//统计出拿过样，却没有采购过大货的产品
	private function repeat_buy($year){
		$data=[];
		$i=1;
		$exe=0;							//0=不缓存    1=生成缓存    2=获取缓存
		if(date('Y')!=$year){			//往年数据缓存，更新需删除缓存文件
			$data=F($this->uuid.'collect/'.$year.'/nor_epeat_buy');
			if($data==""){
				$exe=1;
			}else{
				$exe=2;
			}
		}else{
			$exe=0;
		}

		//读取数据
		if($exe<2){
			//全年有拿样产品
			//$sql1="select a.number,any_value(a.name) as name from ".env('database.prefix')."deal_info as a inner join ".env('database.prefix')."deal_main as b on a.did=b.id where b.status=1 and FROM_UNIXTIME(b.create_time,'%Y')=".$year." and (a.unit='码' or a.unit='米') GROUP BY a.number";
			$sql1="select a.number,name from ".env('database.prefix')."deal_info as a inner join ".env('database.prefix')."deal_main as b on a.did=b.id where b.uuid = ".$this->uuid." and b.status=1 and b.year=".$year." and (a.unit='码' or a.unit='米') GROUP BY a.number";

			//全年拿大货产品
			//$sql2="select a.number,any_value(a.name) as name from ".env('database.prefix')."deal_info as a inner join ".env('database.prefix')."deal_main as b on a.did=b.id where b.status=1 and FROM_UNIXTIME(b.create_time,'%Y')=".$year." and a.roll>0 GROUP BY a.number";
			$sql2="select a.number,name from ".env('database.prefix')."deal_info as a inner join ".env('database.prefix')."deal_main as b on a.did=b.id where b.uuid = ".$this->uuid." and b.status=1 and b.year=".$year." and a.roll>0 GROUP BY a.number";
			$res1=Db::query($sql1);
			$res2=Db::query($sql2);
			$res2=set_key($res2,'number');
			foreach ($res1 as $key=>$val) {
				if(isset($res2[$val['number']])){
					unset($res2[$val['number']]);
				}else{
					$data[]=$val;
				}
			}
			if($exe==1){
				F($this->uuid.'collect/'.$year.'/nor_epeat_buy',$data);
			}
		}
		return $data;
	}

}
?>